CREATE TABLE jms_dws.dws_departure_transfer_timely_route_agg (
                                                                 dt                     DATE COMMENT '日期',
                                                                 start_code varchar(50) comment '始发地 发件中心',
                                                                 order_source_code varchar(50) comment '订单来源:1.桃花岛，2.抖音-紫金山，3.快手-逍遥峰，4.其他',
                                                                 start_name varchar(300) comment'始发地 发件中心',
                                                                 order_source_name varchar(300) comment'订单来源',
                                                                 center_type int comment '中心类型,1始发2中转',
                                                                 start_agent_code varchar(50) comment '发件代理区',
                                                                 start_agent_name varchar(300) comment'发件代理区',
                                                                 send_account bigint comment '发件票数',
                                                                 route_lack_account bigint comment '静态路由缺失票数',
                                                                 transfer_intime_account bigint comment '转运及时票数',
                                                                 transfer_intime_rate double comment '出港转运及时率',
                                                                 without_arrival_account bigint comment '无到达时间票数',
                                                                 error_send_account bigint comment '错发票数',
                                                                 opp_delay_account bigint comment '操作延误票数',
                                                                 send_delay_account bigint comment '发车晚点票数',
                                                                 shipment_delay_account bigint comment '任务单问题票数',
                                                                 stop_delay_account bigint comment '经停点问题票数',
                                                                 all_line_transfer_intime_account bigint comment '全链路-转运及时票数',
                                                                 all_line_transfer_intime_rate double comment '全链路-转运及时率',
                                                                 shipment_error_account bigint COMMENT '任务单错用票数'
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `start_code`,`order_source_code`)
COMMENT '出港转运及时率汇总'
PARTITION BY RANGE(`dt`)
(

PARTITION  p20210925 VALUES [("2021-11-25"), ("2021-11-26")),
PARTITION  p20210926 VALUES [("2021-11-26"), ("2021-11-27")),
PARTITION  p20210927 VALUES [("2021-11-27"), ("2021-11-28")),
PARTITION  p20210928 VALUES [("2021-11-28"), ("2021-11-29")),
PARTITION  p20210929 VALUES [("2021-11-29"), ("2021-11-30")),
PARTITION  p20210930 VALUES [("2021-11-30"), ("2021-12-01")),
PARTITION  p20211001 VALUES [("2021-12-01"), ("2021-12-02")),
PARTITION  p20211002 VALUES [("2021-12-02"), ("2021-12-03")),
PARTITION  p20211003 VALUES [("2021-12-03"), ("2021-12-04")))
DISTRIBUTED BY HASH(`start_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);


ALTER TABLE jms_dws.dws_departure_transfer_timely_route_agg ADD COLUMNS (
    `shipment_error_account` bigint COMMENT '任务单错用票数'
    )


CREATE TABLE `jms_dwd.dwd_departure_transfer_timely_route` (
                                                               `dt` date NULL COMMENT "日期",
                                                               `billcode` varchar(80) NULL COMMENT "运单号",
                                                               `start_code` varchar(80) NULL COMMENT "始发地 发件中心",
                                                               `order_source_code` varchar(80) NULL COMMENT "订单来源:1.桃花岛，2.抖音-紫金山，3.快手-逍遥峰，4.其他",
                                                               `start_name` varchar(300) NULL COMMENT "始发地 发件中心",
                                                               `order_source_name` varchar(300) NULL COMMENT "订单来源",
                                                               `start_agent_code` varchar(80) NULL COMMENT "发件代理区",
                                                               `start_agent_name` varchar(300) NULL COMMENT "发件代理区",
                                                               `center_type` int(11) NULL COMMENT "中心类型,1始发2中转",
                                                               `nextstationcode` varchar(80) NULL COMMENT "中心发件下一站",
                                                               `nextstation` varchar(300) NULL COMMENT "中心发件下一站",
                                                               `send_scantime` datetime NULL COMMENT "发件扫描时间",
                                                               `send_shipment_no` varchar(80) NULL COMMENT "发车任务号",
                                                               `planned_departure_time` datetime NULL COMMENT "规划发车时间",
                                                               `actual_departure_time` datetime NULL COMMENT "实际发车时间",
                                                               `next_planned_arrival_time` datetime NULL COMMENT "下一站规划到达时间",
                                                               `packagecode` varchar(80) NULL COMMENT "所属包号",
                                                               `goods_type_code` varchar(80) NULL COMMENT "物品类型",
                                                               `goods_type_name` varchar(300) NULL COMMENT "物品类型",
                                                               `arrival_shipment_no` varchar(80) NULL COMMENT "到件任务号",
                                                               `arrival_time` datetime NULL COMMENT "到件时间",
                                                               `all_line_arrival_time` datetime NULL COMMENT "全链路到件时间",
                                                               `last_network_code` varchar(80) NULL COMMENT "到件任务对应上一站机构",
                                                               `last_network_name` varchar(300) NULL COMMENT "到件任务对应上一站机构",
                                                               `transfer_hours` double NULL COMMENT "中转时长",
                                                               `route_send_time` datetime NULL COMMENT "应发件时间",
                                                               `all_line_route_send_time` datetime NULL COMMENT "全链路应发件时间",
                                                               `is_timely` int(11) NULL COMMENT "是否及时 1及时,2路由缺失,3不及时",
                                                               `untimely_cause` int(11) NULL COMMENT "不及时原因:1.路由缺失，2.错发，3.任务单问题，4.发车晚点，5.操作延误，6.经停线问题，7.无到达时间,8.及时，9.其他",
                                                               `all_line_is_timely` int(11) NULL COMMENT "全链路是否及时，1及时2路由缺失3不及时",
                                                               `all_line_untimely_cause` int(11) NULL COMMENT "全链路不及时原因:1.路由缺失，2.错发，3.任务单问题，4.发车晚点，5.操作延误，6.经停线问题，7.无到达时间,8.及时，9.其他"
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `billcode`, `start_code`, `order_source_code`)
COMMENT "出港转运及时率明细"
PARTITION BY RANGE(`dt`)
(PARTITION p20210912 VALUES [('2021-09-12'), ('2021-09-13')),
PARTITION p20210913 VALUES [('2021-09-13'), ('2021-09-14')),
PARTITION p20210914 VALUES [('2021-09-14'), ('2021-09-15')),
PARTITION p20210915 VALUES [('2021-09-15'), ('2021-09-16')),
PARTITION p20210916 VALUES [('2021-09-16'), ('2021-09-17')),
PARTITION p20210917 VALUES [('2021-09-17'), ('2021-09-18')),
PARTITION p20210918 VALUES [('2021-09-18'), ('2021-09-19')),
PARTITION p20210919 VALUES [('2021-09-19'), ('2021-09-20')),
PARTITION p20210920 VALUES [('2021-09-20'), ('2021-09-21')),
PARTITION p20210921 VALUES [('2021-09-21'), ('2021-09-22')),
PARTITION p20210922 VALUES [('2021-09-22'), ('2021-09-23')),
PARTITION p20210923 VALUES [('2021-09-23'), ('2021-09-24')),
PARTITION p20210924 VALUES [('2021-09-24'), ('2021-09-25')),
PARTITION p20210925 VALUES [('2021-09-25'), ('2021-09-26')),
PARTITION p20210926 VALUES [('2021-09-26'), ('2021-09-27')),
PARTITION p20210927 VALUES [('2021-09-27'), ('2021-09-28')),
PARTITION p20210928 VALUES [('2021-09-28'), ('2021-09-29')),
PARTITION p20210929 VALUES [('2021-09-29'), ('2021-09-30')),
PARTITION p20210930 VALUES [('2021-09-30'), ('2021-10-01')),
PARTITION p20211001 VALUES [('2021-10-01'), ('2021-10-02')),
PARTITION p20211002 VALUES [('2021-10-02'), ('2021-10-03')),
PARTITION p20211003 VALUES [('2021-10-03'), ('2021-10-04')),
PARTITION p20211004 VALUES [('2021-10-04'), ('2021-10-05')),
PARTITION p20211005 VALUES [('2021-10-05'), ('2021-10-06')),
PARTITION p20211006 VALUES [('2021-10-06'), ('2021-10-07')),
PARTITION p20211007 VALUES [('2021-10-07'), ('2021-10-08')),
PARTITION p20211008 VALUES [('2021-10-08'), ('2021-10-09')),
PARTITION p20211009 VALUES [('2021-10-09'), ('2021-10-10')),
PARTITION p20211010 VALUES [('2021-10-10'), ('2021-10-11')),
PARTITION p20211011 VALUES [('2021-10-11'), ('2021-10-12')),
PARTITION p20211012 VALUES [('2021-10-12'), ('2021-10-13')),
PARTITION p20211013 VALUES [('2021-10-13'), ('2021-10-14')),
PARTITION p20211014 VALUES [('2021-10-14'), ('2021-10-15')),
PARTITION p20211015 VALUES [('2021-10-15'), ('2021-10-16')),
PARTITION p20211016 VALUES [('2021-10-16'), ('2021-10-17')),
PARTITION p20211017 VALUES [('2021-10-17'), ('2021-10-18')),
PARTITION p20211018 VALUES [('2021-10-18'), ('2021-10-19')),
PARTITION p20211019 VALUES [('2021-10-19'), ('2021-10-20')),
PARTITION p20211020 VALUES [('2021-10-20'), ('2021-10-21')),
PARTITION p20211021 VALUES [('2021-10-21'), ('2021-10-22')),
PARTITION p20211022 VALUES [('2021-10-22'), ('2021-10-23')),
PARTITION p20211023 VALUES [('2021-10-23'), ('2021-10-24')),
PARTITION p20211024 VALUES [('2021-10-24'), ('2021-10-25')),
PARTITION p20211025 VALUES [('2021-10-25'), ('2021-10-26')),
PARTITION p20211026 VALUES [('2021-10-26'), ('2021-10-27')),
PARTITION p20211027 VALUES [('2021-10-27'), ('2021-10-28')),
PARTITION p20211028 VALUES [('2021-10-28'), ('2021-10-29')),
PARTITION p20211029 VALUES [('2021-10-29'), ('2021-10-30')),
PARTITION p20211030 VALUES [('2021-10-30'), ('2021-10-31')),
PARTITION p20211031 VALUES [('2021-10-31'), ('2021-11-01')),
PARTITION p20211101 VALUES [('2021-11-01'), ('2021-11-02')),
PARTITION p20211102 VALUES [('2021-11-02'), ('2021-11-03')),
PARTITION p20211103 VALUES [('2021-11-03'), ('2021-11-04')),
PARTITION p20211104 VALUES [('2021-11-04'), ('2021-11-05')),
PARTITION p20211105 VALUES [('2021-11-05'), ('2021-11-06')),
PARTITION p20211106 VALUES [('2021-11-06'), ('2021-11-07')),
PARTITION p20211107 VALUES [('2021-11-07'), ('2021-11-08')),
PARTITION p20211108 VALUES [('2021-11-08'), ('2021-11-09')),
PARTITION p20211109 VALUES [('2021-11-09'), ('2021-11-10')),
PARTITION p20211110 VALUES [('2021-11-10'), ('2021-11-11')),
PARTITION p20211111 VALUES [('2021-11-11'), ('2021-11-12')),
PARTITION p20211112 VALUES [('2021-11-12'), ('2021-11-13')),
PARTITION p20211113 VALUES [('2021-11-13'), ('2021-11-14')),
PARTITION p20211114 VALUES [('2021-11-14'), ('2021-11-15')),
PARTITION p20211115 VALUES [('2021-11-15'), ('2021-11-16')),
PARTITION p20211116 VALUES [('2021-11-16'), ('2021-11-17')),
PARTITION p20211117 VALUES [('2021-11-17'), ('2021-11-18')),
PARTITION p20211118 VALUES [('2021-11-18'), ('2021-11-19')),
PARTITION p20211119 VALUES [('2021-11-19'), ('2021-11-20')),
PARTITION p20211120 VALUES [('2021-11-20'), ('2021-11-21')),
PARTITION p20211121 VALUES [('2021-11-21'), ('2021-11-22')),
PARTITION p20211122 VALUES [('2021-11-22'), ('2021-11-23')),
PARTITION p20211123 VALUES [('2021-11-23'), ('2021-11-24')),
PARTITION p20211124 VALUES [('2021-11-24'), ('2021-11-25')),
PARTITION p20211125 VALUES [('2021-11-25'), ('2021-11-26')),
PARTITION p20211126 VALUES [('2021-11-26'), ('2021-11-27')),
PARTITION p20211127 VALUES [('2021-11-27'), ('2021-11-28')),
PARTITION p20211128 VALUES [('2021-11-28'), ('2021-11-29')),
PARTITION p20211129 VALUES [('2021-11-29'), ('2021-11-30')),
PARTITION p20211130 VALUES [('2021-11-30'), ('2021-12-01')),
PARTITION p20211201 VALUES [('2021-12-01'), ('2021-12-02')),
PARTITION p20211202 VALUES [('2021-12-02'), ('2021-12-03')),
PARTITION p20211203 VALUES [('2021-12-03'), ('2021-12-04')),
PARTITION p20211204 VALUES [('2021-12-04'), ('2021-12-05')),
PARTITION p20211205 VALUES [('2021-12-05'), ('2021-12-06')),
PARTITION p20211206 VALUES [('2021-12-06'), ('2021-12-07')),
PARTITION p20211207 VALUES [('2021-12-07'), ('2021-12-08')),
PARTITION p20211208 VALUES [('2021-12-08'), ('2021-12-09')),
PARTITION p20211209 VALUES [('2021-12-09'), ('2021-12-10')),
PARTITION p20211210 VALUES [('2021-12-10'), ('2021-12-11')),
PARTITION p20211211 VALUES [('2021-12-11'), ('2021-12-12')),
PARTITION p20211212 VALUES [('2021-12-12'), ('2021-12-13')),
PARTITION p20211213 VALUES [('2021-12-13'), ('2021-12-14')),
PARTITION p20211214 VALUES [('2021-12-14'), ('2021-12-15')),
PARTITION p20211215 VALUES [('2021-12-15'), ('2021-12-16')),
PARTITION p20211216 VALUES [('2021-12-16'), ('2021-12-17')),
PARTITION p20211217 VALUES [('2021-12-17'), ('2021-12-18')),
PARTITION p20211218 VALUES [('2021-12-18'), ('2021-12-19')),
PARTITION p20211219 VALUES [('2021-12-19'), ('2021-12-20')))
DISTRIBUTED BY HASH(`billcode`) BUCKETS 10
PROPERTIES (
   "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-100",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "10",
    "in_memory" = "false",
    "storage_format" = "V2"
);

CREATE TABLE `jms_dws.dws_departure_transfer_timely_route_agg` (
                                                                   `dt` date NULL COMMENT "日期",
                                                                   `start_code` varchar(50) NULL COMMENT "始发地 发件中心",
                                                                   `order_source_code` varchar(50) NULL COMMENT "订单来源:1.桃花岛，2.抖音-紫金山，3.快手-逍遥峰，4.其他",
                                                                   `start_name` varchar(300) NULL COMMENT "始发地 发件中心",
                                                                   `order_source_name` varchar(300) NULL COMMENT "订单来源",
                                                                   `center_type` int(11) NULL COMMENT "中心类型,1始发2中转",
                                                                   `start_agent_code` varchar(50) NULL COMMENT "发件代理区",
                                                                   `start_agent_name` varchar(300) NULL COMMENT "发件代理区",
                                                                   `send_account` bigint(20) NULL COMMENT "发件票数",
                                                                   `route_lack_account` bigint(20) NULL COMMENT "静态路由缺失票数",
                                                                   `transfer_intime_account` bigint(20) NULL COMMENT "转运及时票数",
                                                                   `transfer_intime_rate` double NULL COMMENT "出港转运及时率",
                                                                   `without_arrival_account` bigint(20) NULL COMMENT "无到达时间票数",
                                                                   `error_send_account` bigint(20) NULL COMMENT "错发票数",
                                                                   `opp_delay_account` bigint(20) NULL COMMENT "操作延误票数",
                                                                   `send_delay_account` bigint(20) NULL COMMENT "发车晚点票数",
                                                                   `shipment_delay_account` bigint(20) NULL COMMENT "任务单问题票数",
                                                                   `stop_delay_account` bigint(20) NULL COMMENT "经停点问题票数",
                                                                   `all_line_transfer_intime_account` bigint(20) NULL COMMENT "全链路-转运及时票数",
                                                                   `all_line_transfer_intime_rate` double NULL COMMENT "全链路-转运及时率",
                                                                   `shipment_error_account` bigint(20) NULL COMMENT "任务单错用票数"
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `start_code`, `order_source_code`)
COMMENT "出港转运及时率汇总"
PARTITION BY RANGE(`dt`)
(PARTITION p20210925 VALUES [('2021-11-25'), ('2021-11-26')),
PARTITION p20210926 VALUES [('2021-11-26'), ('2021-11-27')),
PARTITION p20210927 VALUES [('2021-11-27'), ('2021-11-28')),
PARTITION p20210928 VALUES [('2021-11-28'), ('2021-11-29')),
PARTITION p20210929 VALUES [('2021-11-29'), ('2021-11-30')),
PARTITION p20210930 VALUES [('2021-11-30'), ('2021-12-01')),
PARTITION p20211001 VALUES [('2021-12-01'), ('2021-12-02')),
PARTITION p20211002 VALUES [('2021-12-02'), ('2021-12-03')),
PARTITION p20211003 VALUES [('2021-12-03'), ('2021-12-04')),
PARTITION p20211204 VALUES [('2021-12-04'), ('2021-12-05')),
PARTITION p20211205 VALUES [('2021-12-05'), ('2021-12-06')),
PARTITION p20211206 VALUES [('2021-12-06'), ('2021-12-07')),
PARTITION p20211207 VALUES [('2021-12-07'), ('2021-12-08')),
PARTITION p20211208 VALUES [('2021-12-08'), ('2021-12-09')),
PARTITION p20211209 VALUES [('2021-12-09'), ('2021-12-10')),
PARTITION p20211210 VALUES [('2021-12-10'), ('2021-12-11')),
PARTITION p20211211 VALUES [('2021-12-11'), ('2021-12-12')),
PARTITION p20211212 VALUES [('2021-12-12'), ('2021-12-13')),
PARTITION p20211213 VALUES [('2021-12-13'), ('2021-12-14')),
PARTITION p20211214 VALUES [('2021-12-14'), ('2021-12-15')),
PARTITION p20211215 VALUES [('2021-12-15'), ('2021-12-16')),
PARTITION p20211216 VALUES [('2021-12-16'), ('2021-12-17')),
PARTITION p20211217 VALUES [('2021-12-17'), ('2021-12-18')),
PARTITION p20211218 VALUES [('2021-12-18'), ('2021-12-19')),
PARTITION p20211219 VALUES [('2021-12-19'), ('2021-12-20')))
DISTRIBUTED BY HASH(`start_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);

ALTER TABLE jms_dws.dws_departure_transfer_timely_route_agg
    ADD COLUMN mage_region_code STRING KEY comment '管理大区编码' AFTER order_source_code;

ALTER TABLE jms_dws.dws_departure_transfer_timely_route_agg
    ADD COLUMN mage_region_name STRING  comment '管理大区名称' AFTER mage_region_code;


ALTER TABLE jms_dws.dws_departure_transfer_timely_route_agg add COLUMN(
reserved_seconds BIGINT(20) null COMMENT '操作时长',
start_provider_id  VARCHAR(100) COMMENT '始发省份',
start_provider_desc  VARCHAR(100) COMMENT '始发省份'
)